package fr.upmf.miam.imageannotation;

import android.provider.ContactsContract;
import android.provider.MediaStore.Images;
import android.util.Log;
import android.content.*;
import android.database.Cursor;
import android.database.CursorJoiner;
import android.database.MatrixCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;

public class ImageAnnotationProvider extends ContentProvider {

    private static final UriMatcher uriMatcher;
    private AnnotDBHelper dbHelper;

    private static final int DEFAULT = 0;
    private static final int PEOPLE = 1;
    private static final int PICTURE = 2;
    private static final int PEOPLE_ID = 3;
    private static final int PICTURE_ID = 4;
    private static final int PEOPLE_ID_PICTURE = 5;
    private static final int PICTURE_ID_PEOPLE = 6;
    private static final int PEOPLES = 7;

    class C extends ImageAnnotationContract {
    }

    static {
	uriMatcher = new UriMatcher(DEFAULT);
	uriMatcher.addURI(C.AUTHORITY, C.People.TABLE_NAME, PEOPLE);
	uriMatcher.addURI(C.AUTHORITY, C.People.TABLE_NAME + "/S", PEOPLES);
	uriMatcher.addURI(C.AUTHORITY, C.Picture.TABLE_NAME, PICTURE);

	uriMatcher.addURI(C.AUTHORITY, C.People.TABLE_NAME + "/#", PEOPLE_ID);
	uriMatcher.addURI(C.AUTHORITY, C.Picture.TABLE_NAME + "/#", PICTURE_ID);

	uriMatcher.addURI(C.AUTHORITY, C.People.TABLE_NAME + "/#/" + C.People.Picture.CONTENT_PATH, PEOPLE_ID_PICTURE);
	uriMatcher.addURI(C.AUTHORITY, C.Picture.TABLE_NAME + "/#/" + C.Picture.People.CONTENT_PATH, PICTURE_ID_PEOPLE);
    }

    @Override
    public boolean onCreate() {
	dbHelper = new AnnotDBHelper(this.getContext());
	return true;
    }

    @Override
    public String getType(Uri uri) {
	switch (uriMatcher.match(uri)) {
	case PEOPLE: return C.People.CONTENT_TYPE;
	case PICTURE: return C.Picture.CONTENT_TYPE;
	case PEOPLE_ID: return C.People.CONTENT_ITEM_TYPE;
	case PICTURE_ID: return C.Picture.CONTENT_ITEM_TYPE;
	case PEOPLE_ID_PICTURE: return C.Picture.CONTENT_TYPE;
	case PICTURE_ID_PEOPLE: return C.People.CONTENT_TYPE;
	default: throw new IllegalArgumentException("unknown Uri : " + uri.toString());
	}
    }

    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs) {

	SQLiteDatabase db = dbHelper.getWritableDatabase();
	String tableName = null;
	
	switch (uriMatcher.match(uri)) {
	case PEOPLE_ID_PICTURE:
	    tableName = C.Annotation.TABLE_NAME;
	}    
	    
	db.delete(tableName, selection, selectionArgs);
	return 0;
    }

    @Override
    public Uri insert(Uri uri, ContentValues values) {

	String tableName = null;
	Uri newUri = null;
	long peopleId, imgId;
	switch (uriMatcher.match(uri)) {

	/**
	 * /people
	 * @param ImageAnnotationContract.People.URI ~ uri externe du contact
	 * @return /people/ID
	 */
	case PEOPLE:
	    tableName = C.People.TABLE_NAME;

	    String peopleContactUri = values.getAsString(C.People.URI);
	    peopleId = -1;
	    if (peopleContactUri != null) {

		// essaye de récupérer le contact
		Cursor c = this
			.getContext()
			.getContentResolver()
			.query(
			// "SELECT id FROM people WHERE uri = <peopleContactUri>"
			C.People.CONTENT_URI, new String[] { C.People._ID },
				C.People.URI + "=?",
				new String[] { peopleContactUri }, null);

		// le contact existe dans l'appli = on retourne l'url du contact
		if (c.moveToFirst()) {
		    peopleId = c.getLong(0);
		    return Uri.withAppendedPath(uri, String.valueOf(peopleId));

		    // l'image n'existe pas = on prepare l'enregistrement
		} else {
		    tableName = C.People.TABLE_NAME;
		}
	    }
	    break;

	/**
	 * /picture
	 * @param ImageAnnotationContract.Picture.URI ~ uri externe de l'image
	 * @return /picture/ID
	 */
	case PICTURE:
	    String imgMediaUri = values.getAsString(C.Picture.URI);
	    imgId = -1;
	    if (imgMediaUri != null) {

		// essaye de récupérer l'image
		Cursor c = this
			.getContext()
			.getContentResolver()
			.query(
			// "SELECT id FROM picture WHERE uri = <imgExternalUri>"
			C.Picture.CONTENT_URI, new String[] { C.Picture._ID },
				C.Picture.URI + "=?",
				new String[] { imgMediaUri }, null);

		// l'image existe = on retourne l'url de l'image
		if (c.moveToFirst()) {
		    imgId = c.getLong(0);
		    return Uri.withAppendedPath(uri, String.valueOf(imgId));

		    // l'image n'existe pas = on prepare l'enregistrement
		} else {
		    tableName = C.Picture.TABLE_NAME;
		}
	    }
	    break;

	/**
	 * /people/ID/picture
	 * @param ImageAnnotationContract.People.Picture.URI ~ uri externe de l'image OU
	 * @param ImageAnnotationContract.Annotation.PICTURE_ID ~ id de l'image dans l'appli
	 * @return /people/ID/picture
	 */
	case PEOPLE_ID_PICTURE:
	    String imgExternalUri = values.getAsString(C.People.Picture.URI);
	    imgId = -1;

	    // si l'utilisateur fourni l'Uri externe de l'image
	    if (imgExternalUri != null) {

		// essaye de récupérer l'image : "SELECT id FROM picture WHERE uri = <imgExternalUri>"
		Cursor c = this.getContext().getContentResolver().query(
			C.Picture.CONTENT_URI, 
			new String[] { C.Picture._ID },
			C.Picture.URI + "=?", new String[] { imgExternalUri }, 
			null);

		// l'image existe
		if (c.moveToFirst()) {
		    imgId = c.getLong(0);

		// l'image n'existe pas : on l'enregistre
		} else {
		    Uri uriImg = this.getContext().getContentResolver().insert(C.Picture.CONTENT_URI, values);
		    imgId = ContentUris.parseId(uriImg);
		}

		values.remove(C.People.Picture.URI);
		values.put(C.Annotation.PICTURE_ID, imgId);
	    }

	    // on prepare l'enregistrement (PEOPLE_ID = # : people/#/picture)
	    peopleId = Long.parseLong(uri.getPathSegments().get(1));
	    values.put(C.Annotation.PEOPLE_ID, peopleId);
	    tableName = C.Annotation.TABLE_NAME;

	    newUri = Uri.withAppendedPath(uri, String.valueOf(imgId));
	    break;

	/**
	 * Ça ne devrait pas arriver
	 */
	default: throw new IllegalArgumentException(("uri inconnu : " + uri.toString()));
	}

	// Enregistrement
	SQLiteDatabase db = dbHelper.getWritableDatabase();
	long id = db.insert(tableName, null, values);

	if (id > -1 && newUri == null) {
	    // l'uri de la ligne créée est la concaténation de l'uri de base + "/" + id généré
	    newUri = Uri.withAppendedPath(uri, String.valueOf(id));
	}
	return newUri;
    }

    /*******************************************************************************
     * rechercher des données dans la BD pour toutes les ressources gérées par
     * le Provider
     * 
     * @see http://developer.android.com/reference/android/content/ContentProvider.html
     * 
     * @param uri ~ uri de la ressource
     * @param projection ~ noms des colones à retourner (SELECT)
     * @param selection ~ la clause WHERE. Peut contenir des ? qui seront des arguments (selectionArgs)
     * @param selectionArgs ~ tableau des valeurs des arguments de la clause WHERE
     * @param sortOrder ~ la clause ORDER BY
     * 
     * @return le résultat (Cursor) ou NullPointerException si aucun
     ******************************************************************************/
    public Cursor query(Uri uri, String[] projection, String selection,
	    String[] selectionArgs, String sortOrder) {

	SQLiteDatabase db = dbHelper.getWritableDatabase();
	Cursor c, cc, ccc;
	String table = "";
	switch (uriMatcher.match(uri)) {

	/** 
	 * /people 
	 * = liste de personnes
	 */
	case PEOPLES:
	    
	    /*
	     * SELECT * FROM <contacts_uri>
	     */
	    c = this.getContext().getContentResolver().query(
			ContactsContract.Contacts.CONTENT_URI, 
			null, //new String[] { ContactsContract.Contacts._ID, ContactsContract.Contacts.DISPLAY_NAME } 
			null, null, ContactsContract.Contacts._ID
		);
	    
	    /*
	     * SELECT * FROM people
	     */
	    cc = db.query(
		    C.People.TABLE_NAME  + " LEFT JOIN " + C.Annotation.TABLE_NAME 
		    + " ON " + C.People.TABLE_NAME + "." + C.People._ID + "=" + C.Annotation.PEOPLE_ID,
		    new String[] { 
		    	C.People._ID + " as people" + C.People._ID, 
		    	C.People.URI, 
		    	ImageAnnotationProvider.SQL_getId(C.People.URI) + " as people_idPeople" }, 
		    selection, selectionArgs,
		    null, null, "people_idPeople");
	
	    /* 
	     * SELECT <projection> FROM <people_uri>
	     * LEFT JOIN people ON people.people_idPeople = <people_uri>._id 
	     */
	    ccc = leftJoin(projection,
		    c, new String[] { ContactsContract.Contacts._ID}, 
		    cc, new String[] { "people_idPeople" }
	    );
	    return ccc;
	    
	/** 
	 * /people 
	 * = liste de personnes ayant au moins une annotation 
	 */
	case PEOPLE:
	    
	    /*
	     * prepare :: 
	     * SELECT <projection> FROM people 		
	     * WHERE <selection>=<selectionArgs> 		
	     */
	     table = C.People.TABLE_NAME;
	     break;

	/** 
	 * /picture 
	 * = liste d'image ayant au moins une annotation
	 */
	case PICTURE:
	    
	    /* 
	     * prepare :: 
	     * SELECT <projection> FROM picture 
	     * WHERE <selection>=<selectionArgs>
	     */
	    table = C.Picture.TABLE_NAME;
	    break;

	/**
	 * /people/# 
	 * = infos sur la personne 
	 */
	case PEOPLE_ID:
	    
	    /* 
	     * SELECT _id, uri FROM people 
	     * WHERE _id = #
	     */
	    c = db.query(C.People.TABLE_NAME, 
		    new String[] { 
		    	C.People._ID + " as people" + C.People._ID, 
		    	C.People.URI,
		    	SQL_getId(C.People.URI) + " as people_idPeople"
		    }, 
		    C.People._ID + "=?", new String[] { uri.getPathSegments().get(1) }, 
		    null, null, sortOrder
	    );

	    String contact = "";
	    if (c.moveToFirst()) { contact = c.getString(1); } else { throw new IllegalArgumentException(("contact inconnu : " + uri.toString())); }

	    /* 
	     * SELECT * FROM <contact_uri>
	     */
	    cc = this.getContext().getContentResolver().query(
		    Uri.parse(contact), 
		    projection, 
		    null, null, null);
	    
	    /* 
	     * SELECT <projection> FROM people, <people_uri>
	     * WHERE <people_uri>._id = people.people_idPeople 
	     */
	    ccc = innerJoin(projection, 
		    c, new String[] { "people_idPeople" }, 
		    cc, new String[] { ContactsContract.Contacts._ID}
	    );
	    return ccc;

	/**
	 * /picture/# 
	 * = infos sur l'image 
	 */
	case PICTURE_ID:
	    
	    /* 
	     * SELECT picture_id, uri, picture_idImg FROM picture 
	     * WHERE _id = #
	     */
	    c = db.query(C.Picture.TABLE_NAME, 
		new String[] {
		    C.Picture._ID + " as picture" + C.Picture._ID,
		    C.Picture.URI,
		    SQL_getId(C.People.URI) + " as picture_idImg", 
    	    	},
    	    	C.Picture._ID + "=?", new String[] { uri.getPathSegments().get(1) }, 
    	    	null, null, sortOrder
    	    );

	    String get = "";
	    if (c.moveToFirst()) { get = c.getString(1); } else { throw new IllegalArgumentException(("image inconnu : " + uri.toString())); }

	    /* 
	     * SELECT * FROM <picture_uri>
	     */
	    cc = this.getContext().getContentResolver().query(
		    Uri.parse(get), 
		    null, null, null, null
	    );

	    /* 
	     * SELECT <projection> FROM picture, <picture_uri>
	     * WHERE <picture_uri>._id = picture.picture_idImg 
	     */
	    ccc = innerJoin(projection, 
		    c, new String[] { "picture_idImg" }, 
		    cc, new String[] { Images.Media._ID }
	    );
	    return ccc;

	/**
	 * /people/#/picture 
	 * = liste d'images où apparait la personne 
	 */
	case PEOPLE_ID_PICTURE:

	    /* 
	     * prepare :: 
	     * SELECT <projection> FROM annotation 
	     * INNER JOIN picture ON annotation.picture_id = picture._id
	     * WHERE annotation.people_id = #
	     */
	    table = C.Annotation.TABLE_NAME 
		    + " INNER JOIN " + C.Picture.TABLE_NAME + " ON (" 
		    + C.Annotation.TABLE_NAME + '.' + C.Annotation.PICTURE_ID + " = " + C.Picture.TABLE_NAME + '.' + C.Picture._ID 
		    + ")";

	    if(selection == null && selectionArgs == null) { 
		selection = C.Annotation.PEOPLE_ID + "=?";
		selectionArgs = new String[] { uri.getPathSegments().get(1) };
	    }

	    break;

	/**
	 * /picture/#/people = liste de personnes apparaissant sur l'image 
	 */
	case PICTURE_ID_PEOPLE:

	    /* 
	     * SELECT people_id, picture_id, people_idPeople, uri FROM annotation
	     * INNER JOIN people ON annotation.people_id = people._id 
	     * WHERE annotation.picture_id = # 
	     */
	    c = db.query(C.Annotation.TABLE_NAME 
		    + " INNER JOIN " + C.People.TABLE_NAME 
		    + " ON (" + C.Annotation.TABLE_NAME + '.' + C.Annotation.PEOPLE_ID + " = " + C.People.TABLE_NAME + '.' + C.People._ID + ")",
		new String[] {
		    C.Annotation.PEOPLE_ID,
		    C.Annotation.PICTURE_ID,
		    SQL_getId(C.People.URI) + " as people_idPeople",
		    C.Picture.URI 
	    	}, 
		C.Annotation.PICTURE_ID + "=?", new String[] { uri.getPathSegments().get(1) },
		null, null, "people_idPeople");

	    /* 
	     * SELECT * FROM <contact_uri>
	     */
	    cc = this.getContext().getContentResolver().query(
		    ContactsContract.Contacts.CONTENT_URI, 
		    null, null, null, ContactsContract.Contacts._ID);	   

	    /* 
	     * SELECT <projection> FROM people, <people_uri>
	     * WHERE <people_uri>._id = people.people_idPeople 
	     */
	    ccc = innerJoin(null, 
		    c, new String[] { "people_idPeople" }, 
		    cc, new String[] { ContactsContract.Contacts._ID }
	    );
	    return ccc;

	default: throw new IllegalArgumentException("unknown uri : " + uri.toString());
	}

	SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
	builder.setTables(table);

	/*
	 * execute les requetes preparees (cf prepare :: SELECT ...)
	 */
	c = builder.query(db, projection, selection, selectionArgs, null, null, sortOrder);
	c.setNotificationUri(getContext().getContentResolver(), uri);

	return c;
    }

    @Override
    public int update(Uri uri, ContentValues values, String selection,
	    String[] selectionArgs) {
	// TODO Auto-generated method stub
	return 0;
    }

    /**
     * permet d'effectuer un innerJoin sur deux curseur <br>
     * SELECT projection FROM a, b WHERE a.aColumn = b.bColumn
     * 
     * @param projection ~ SELECT, null pour retourner toutes les colonnes
     * @param a
     * @param aColumn ~ colonne(s) du Join pour le curseur a
     * @param b
     * @param bColumn ~ colonne(s) du Join pour le curseur a
     * @return
     */
    private Cursor innerJoin(String[] projection, Cursor a, String[] aColumn, Cursor b, String[] bColumn) {
	    if(projection==null) {
		projection = new String[a.getColumnNames().length + b.getColumnNames().length];
		int i = 0;
	    	for(String s : a.getColumnNames()) { projection[i] = s; i++; }
	    	for(String s : b.getColumnNames()) { projection[i] = s; i++; }
	    }
	    MatrixCursor curs = new MatrixCursor(projection);
	    CursorJoiner cursJoiner = new CursorJoiner(a, aColumn, b, bColumn);
	    for (CursorJoiner.Result joinerResult : cursJoiner) {
		switch (joinerResult) {
		case BOTH: // on veux faire du inner
		    
		    String[] add = new String[projection.length]; 
		    int i = 0;
		    for(String s : projection) {
			add[i] = (a.getColumnIndex(s)!=-1) ? a.getString(a.getColumnIndex(s)) : b.getString(b.getColumnIndex(s));
			i++;
		    }
		    curs.addRow(add);
		    break;
		default: break;
		}
	    }
	    return curs;
    }
    
    private Cursor leftJoin(String[] projection, Cursor a, String[] aColumn, Cursor b, String[] bColumn) {
	    if(projection==null) {
		projection = new String[a.getColumnNames().length + b.getColumnNames().length];
		int i = 0;
	    	for(String s : a.getColumnNames()) { projection[i] = s; i++; }
	    	for(String s : b.getColumnNames()) { projection[i] = s; i++; }
	    }
	    MatrixCursor curs = new MatrixCursor(projection);
	    CursorJoiner cursJoiner = new CursorJoiner(a, aColumn, b, bColumn);
	    for (CursorJoiner.Result joinerResult : cursJoiner) {
		String[] add = new String[projection.length];;
		int i = 0;
		
		switch (joinerResult) {
		case LEFT : // on veux faire du left
		    
		    for(String s : projection) {
			if(a.getColumnIndex(s)!=-1) {
			    add[i] = a.getString(a.getColumnIndex(s));
			    i++;
			}
		    }
		    curs.addRow(add);
		    break;
		case BOTH: // on garde le inner
		    
		    for(String s : projection) {
			add[i] = (a.getColumnIndex(s)!=-1) ? a.getString(a.getColumnIndex(s)) : b.getString(b.getColumnIndex(s));
			i++;
		    }
		    curs.addRow(add);
		    break;
		default: break;
		}
	    }
	    return curs;
}
    
    /**
     * Liaison à la BD
     * 
     */
    private static class AnnotDBHelper extends SQLiteOpenHelper {

	static final int VERSION = 1;
	static final String DBNAME = "imageannotation.db";

	public AnnotDBHelper(Context context) {
	    super(context, DBNAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	    db.execSQL("CREATE TABLE " + C.People.TABLE_NAME + " ( "
		    + C.People._ID + " INTEGER PRIMARY KEY, " + C.People.URI
		    + " TEXT UNIQUE NOT NULL" + ")");
	    db.execSQL("CREATE TABLE " + C.Picture.TABLE_NAME + " ( "
		    + C.People._ID + " INTEGER PRIMARY KEY, " + C.People.URI
		    + " TEXT UNIQUE NOT NULL" + ")");
	    db.execSQL("CREATE TABLE " + C.Annotation.TABLE_NAME + " ( "
		    + C.Annotation.PEOPLE_ID + " INTEGER REFERENCES "
		    + C.People.TABLE_NAME + "(" + C.People._ID + "), "
		    + C.Annotation.PICTURE_ID + " INTEGER REFERENCES "
		    + C.Picture.TABLE_NAME + "(" + C.Picture._ID + "), "
		    + " PRIMARY KEY (" + C.Annotation.PEOPLE_ID + ","
		    + C.Annotation.PICTURE_ID + "))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    // TODO Auto-generated method stub
	}
    }

    /**
     * Prepare la requête pour recuperer le dernier segment d'un path <br>
     * (pour la colonne column, "x de wtf://a/b/x")
     * @param column ~ le nom de la colonne
     * @return SUBSTR(column, 1+LENGTH(column, REPLACE(column, '/')))
     */
    public static String SQL_getId(String column) {
	//length+trim / = lastindexof /
	return "SUBSTR(" + column + ", 1+LENGTH(RTRIM(" + column + ",REPLACE(" + column + ",'/',''))) ) ";
    }
	
    /**
     * Affiche les infos contenues dans le curseur (System.out.println => logcat)
     * @param c ~ le curseur à afficher
     */
    public static void print_r(Cursor c) {
	int k = 0;
	Log.w("PRINTR cursor", "||> print_r (" + c.getCount() + " obj.)");
	while (c.moveToNext()) {
	    System.out.println("|-- obj." + k);
	    k++;
	    for (int i = 0; i < c.getColumnCount(); i++) {
		System.out.println("|   |-- col." + i + " | " + c.getColumnName(i) + " | " + c.getString(i));
	    }
	}
	Log.w("PRINTR", "||>DONE");
    }
    
    /**
     * Affiche les infos contenues dans le tableau de chaîne (System.out.println => logcat)
     * @param foo ~ le tableau à afficher
     */
    public static void print_r(String[] foo) {
	Log.w("PRINTR array", "||> print_r (" + foo.length + " el.)");
	int i = 0;
	for(String s : foo) {
	    System.out.println("|-- " + i + " = " + s);
	    i++;
	}
	Log.w("PRINTR", "||>DONE");
    }
}